IF OBJECT_ID('dbo.vwInfoEdFinancialDeliverables') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwInfoEdFinancialDeliverables
    IF OBJECT_ID('dbo.vwInfoEdFinancialDeliverables') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwInfoEdFinancialDeliverables >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwInfoEdFinancialDeliverables >>>'
END
go
-- =======================================================================
-- Object Name: (VIEW) dbo.vwInfoEdFinancialDeliverables
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 08/19/2008 
--
-- Description: View to Financial Deliverables
--
-- Used BY Procedure/Report: 
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 04/20/08    RM	Created based on vwInfoEdFinancialDeliverables
--
-- =======================================================================
CREATE VIEW [dbo].[vwInfoEdFinancialDeliverables]
AS
      
SELECT    D.prop_no,
          D.ReportId,
          D.ReportName,
          RTRIM(C1.code_desc) AS REPORTCAT,
          SUBSTRING(D.REPORTCAT,LEN('PARTNERS_P_')+1,100) AS REPORTCAT_CODE,
          D.REPORTCAT AS REPORTCAT_CODE_LONG,
          C2.code_desc AS REPORTTYPE,
          D.SubmittedDate,
          D.SubmittedByLastName + ', ' + D.SubmittedByFirstName AS SubmittedBy,
          FE.FullyExecutedDate AS CompletedDate,
          FE.CompletedBy,
          UEAR.UnExecutedAgreementRecdDate,
          UEAR.AgrReceivedBy,
          CASE WHEN dbo.fnRemoveTime(S.ProcessedDate) <= '1/1/2009'
               THEN 'N/A'          
               ELSE CONVERT(VARCHAR(5),DATEDIFF(DD,UEAR.UnExecutedAgreementRecdDate,FE.FullyExecutedDate))
          END AS DaysComplCalc,
          D.DueDate,
          --CASE WHEN S.REPORTSTAT = 'PARTNERS_P_REPORTSTAT_61' AND D.CompletedDate IS NOT NULL
               -- Convert to CSCR - Completed
          --     THEN 'CSCR - Completed'
          --     ELSE RTRIM(C3.code_desc)
          --END AS REPORTSTAT,
	  RTRIM(C3.code_desc) AS REPORTSTAT, --Commenting out above code until we figure out where the business rule came from
          SUBSTRING(S.REPORTSTAT,LEN('PARTNERS_P_')+1,100) AS REPORTSTAT_CODE,
          S.REPORTSTAT AS REPORTSTAT_CODE_LONG,
          S.StatusDate,
          S.StatusByLastName + ', '+ S.StatusByFirstName AS StatusBy,
          -- For SubContracts, FolderNumber should be ReportName...
          CASE WHEN D.REPORTCAT = 'PARTNERS_P_REPORTCAT_34' 
               THEN D.ReportName + ': ' + S.Comments
               ELSE S.Comments
          END AS Comments,
          CASE WHEN D.SubmittedDate IS NOT NULL
               THEN 1
               ELSE 0
          END AS ConvertedInd,
          D.DeliverableID,
          S.ProcessedDate,
          D.AccountNo,
          D.PeriodStartDate,
          D.PeriodEndDate
          
FROM      dbo.InfoEdPTDeliverable D LEFT OUTER JOIN dbo.InfoEdCodetab C1 ON D.REPORTCAT = C1.codeID
               LEFT OUTER JOIN dbo.InfoEdCodetab C2 ON D.REPORTTYPE = C2.codeID
               LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID
               LEFT OUTER JOIN dbo.InfoEdCodetab C3 ON S.REPORTSTAT = C3.codeID
               -- Get FullyExecuted Date for Completed Date (Last Date as FullyExecuted)
               LEFT OUTER JOIN
                    (SELECT   D.ReportID,
                              S.ProcessedDate AS FullyExecutedDate,
                              S.StatusByLastName + ', ' + S.StatusByFirstName AS CompletedBy
                    FROM      dbo.InfoEdPTDeliverable D 
                                   LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID         
                         INNER JOIN 
                              (SELECT     D.ReportId,
                                          MAX(S.ProcessedDate) AS FullyExecutedDate
                                FROM      dbo.InfoEdPTDeliverable D 
                                               LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID         
                                WHERE     S.REPORTSTAT in ('PARTNERS_P_REPORTSTAT_22','PARTNERS_P_REPORTSTAT_31')
                                GROUP BY D.ReportId)  C ON D.ReportID = C.ReportID AND S.ProcessedDate = C.FullyExecutedDate
                    WHERE     S.REPORTSTAT in ('PARTNERS_P_REPORTSTAT_22','PARTNERS_P_REPORTSTAT_31') ) FE ON D.ReportId = FE.ReportId

               -- Get UnExecutedAgreementRecd Date (First Date) for DaysComplCalc (40,41 codes)
               LEFT OUTER JOIN
                    (SELECT   D.ReportID,
                              S.ProcessedDate AS UnExecutedAgreementRecdDate,
                              S.StatusByLastName + ', ' + S.StatusByFirstName AS AgrReceivedBy
                    FROM      dbo.InfoEdPTDeliverable D 
                                   LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID         
                         INNER JOIN 
                              (SELECT     D.ReportId,
                                          MIN(S.ProcessedDate) AS UnExecutedAgreementRecdDate
                                FROM      dbo.InfoEdPTDeliverable D 
                                               LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID         
                                WHERE     S.REPORTSTAT in ('PARTNERS_P_REPORTSTAT_40','PARTNERS_P_REPORTSTAT_41')
                                GROUP BY D.ReportId)  C ON D.ReportID = C.ReportID AND S.ProcessedDate = C.UnExecutedAgreementRecdDate
                    WHERE     S.REPORTSTAT in ('PARTNERS_P_REPORTSTAT_40','PARTNERS_P_REPORTSTAT_41') ) UEAR ON D.ReportId = UEAR.ReportId


WHERE     D.Inst_Code = 'PARTNERS' AND
          D.REPORTCAT IN ('PARTNERS_P_REPORTCAT_1')    -- Financial Report

go

IF OBJECT_ID('dbo.vwInfoEdFinancialDeliverables') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwInfoEdFinancialDeliverables >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwInfoEdFinancialDeliverables >>>'
go

--GRANT EXECUTE ON dbo.vwInfoEdFinancialDeliverables TO InsightReport,Insight
go

